﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DataClassLayer
{
   public class ClubCollection
    {
        public ClubCollection()
        {
            
        }
        public bool Add(Club club)
        {
            try
            {
                List<string> query = new List<string>();
                string command = @"Insert into SOCCER_CLUB_INFO (Name,DateOfFoundation,CoachID,Image,OfficialWebSite,Logo,ColorImage,LeagueID,GeneralInformation,StadiumID) Values
                   ('" + club.Name + "',Convert(datetime,'"
                   + club.DateOfFoundation + "',103),"
                   + club.CoachID + ",'"
                   + club.Image + "','"
                   + club.OfficialWebSite + "','"
                   + club.Logo + "','"
                   + club.ColorImage + "',"
                   + club.LeagueID + ",'"
                   + club.GeneralInformation + "',"
                   + club.StaduimID + ")";
                query.Add(command);
                DataAccessObject.DataBase.CreateUpdateDelete(query);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool Edit(Club club)
        {
            try
            {
                List<string> query = new List<string>();
                string command = @"Update SOCCER_CLUB_INFO Set 
                     Name='" + club.Name +  
                     "',DateOfFoundation=Convert(datetime,'" + club.DateOfFoundation + 
                     "',103),CoachID=" + club.CoachID + 
                     ",Image='" + club.Image + 
                     "',OfficialWebSite='" + club.OfficialWebSite + 
                     "',Logo='" + club.Logo + 
                     "',ColorImage='" + club.ColorImage + 
                     "',LeagueID="+ club.LeagueID + 
                     ",GeneralInformation='" + club.GeneralInformation + 
                     "',StadiumID=" + club.StaduimID + " Where ID=" + club.ID;
                query.Add(command);
                DataAccessObject.DataBase.CreateUpdateDelete(query);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool Delete(Club club)
        {
            try
            {
                List<string> query = new List<string>();
                string command = @"Delete From SOCCER_CLUB_INFO Where ID= " + club.ID; 
                query.Add(command);
                DataAccessObject.DataBase.CreateUpdateDelete(query);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public DataTable FillList(Club club)
        {
            try
            {
                string wherestatement = WhereStatement(club);
                string command = @"SELECT        SOCCER_CLUB_INFO.ID, SOCCER_CLUB_INFO.Name, Convert(Datetime,SOCCER_CLUB_INFO.DateOfFoundation,103) as DateOfFoundation, SOCCER_CLUB_INFO.CoachID, 
                         SOCCER_COATCHES.Name AS CoatchName, SOCCER_CLUB_INFO.Image, SOCCER_CLUB_INFO.OfficialWebSite, SOCCER_CLUB_INFO.Logo, 
                         SOCCER_CLUB_INFO.ColorImage, SOCCER_CLUB_INFO.LeagueID, SOCCER_LEAGUES.Name AS LeagueName, SOCCER_CLUB_INFO.GeneralInformation, 
                         SOCCER_CLUB_INFO.StadiumID, SOCCER_STADIUMS.Name AS StaduimName
                         FROM            SOCCER_CLUB_INFO INNER JOIN
                         SOCCER_COATCHES ON SOCCER_CLUB_INFO.CoachID = SOCCER_COATCHES.ID INNER JOIN
                         SOCCER_LEAGUES ON SOCCER_CLUB_INFO.LeagueID = SOCCER_LEAGUES.ID INNER JOIN
                         SOCCER_STADIUMS ON SOCCER_CLUB_INFO.StadiumID = SOCCER_STADIUMS.ID";
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    command += " Where " + wherestatement;
                }
                DataTable table = DataAccessObject.DataBase.ExcuteQuery(command);
                return table;
            }
            catch
            {
                return null;
            }
        }
        public int MaxID()
        {
            try
            {
                int Maxid = 1;
                string command = @"SELECT    Max(ID) AS MaxID FROM   SOCCER_CLUB_INFO";
                DataTable table = DataAccessObject.DataBase.ExcuteQuery(command);
                if (table.Rows.Count == 1)
                {
                    Maxid = int.Parse(table.Rows[0]["MaxID"].ToString());
                }
                return Maxid;
            }
            catch
            {
                return 1;
            }
        }
        public DataTable FillListPlayers(Club club)
        {
            try
            {
                string command = @"SELECT        SOCCER_SINGLE_PLAYER_INFO.Name AS PlayerName, SOCCER_SINGLE_PLAYER_INFO.ID AS PlayerID
                                     FROM            SOCCER_CLUB_INFO INNER JOIN
                         SOCCER_SINGLE_PLAYER_INFO ON SOCCER_CLUB_INFO.ID = SOCCER_SINGLE_PLAYER_INFO.ClubID Where SOCCER_SINGLE_PLAYER_INFO.ClubID =" + club.ID;
                DataTable table = DataAccessObject.DataBase.ExcuteQuery(command);
                return table;
            }
            catch
            {
                return null;
            }
        }
        public string WhereStatement(Club club)
        {
            string wherestatement = "";
            if (club.ID > 0)
            {
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    wherestatement += "And SOCCER_CLUB_INFO.ID= " + club.ID;
                }
                else
                {
                    wherestatement += "SOCCER_CLUB_INFO.ID= " + club.ID;
                }
            }
            if (club.Name != null &&
                club.Name != "")
            {
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    wherestatement += "And SOCCER_CLUB_INFO.Name Like '%" + club.Name + "%' ";
                }
                else
                {
                    wherestatement += " SOCCER_CLUB_INFO.Name Like '%" + club.Name + "%' ";
                }
            }
            if (club.LeagueName != null &&
                club.LeagueName != "")
            {
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    wherestatement += "And SOCCER_LEAGUES.Name Like '%" + club.LeagueName + "%' ";
                }
                else
                {
                    wherestatement += " SOCCER_LEAGUES.Name Like '%" + club.LeagueName + "%' ";
                }
            }
            return wherestatement;
        }
    }
}
